package cn.eeepay.framework.dao.aatAgentActivity;

import cn.eeepay.framework.db.pagination.Page;
import cn.eeepay.framework.model.aatAgentActivity.*;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;
import java.util.Map;

public interface AgentActivityDao {

    @SelectProvider(type= AgentActivityDao.SqlProvider.class,method="selectAllList")
    @ResultType(AgentActivity.class)
    List<AgentActivity> selectAllList(@Param("info")AgentActivity info,@Param("page") Page<AgentActivity> page);

    @Update(
            " update aat_agent_activity set open_status=#{sta} where id=#{id}"
    )
    int saveSwitch(@Param("id")int id, @Param("sta")String sta);

    /**
     * 新增主活动数据
     * @param info
     * @return
     */
    @Insert(
            " INSERT INTO aat_agent_activity " +
                    " (act_code,act_name,act_desc,act_rule,act_link,open_status,act_begin_time,act_end_time,create_time,act_img,operator) " +
                    " VALUES " +
                    " (#{info.actCode},#{info.actName},#{info.actDesc},#{info.actRule},#{info.actLink},'0',#{info.actBeginTime},#{info.actEndTime},now(),null,#{info.operator})"
    )
    int addAgentActivity(@Param("info")AgentActivity info);

    /**
     * 修改活动 主表
     * @param info
     * @return
     */
    @Update(
            "update aat_agent_activity set " +
                    "   act_name=#{info.actName},act_desc=#{info.actDesc},act_rule=#{info.actRule},act_begin_time=#{info.actBeginTime},act_end_time=#{info.actEndTime} " +
                    " where act_code=#{info.actCode}"
    )
    int editAgentActivity(@Param("info")AgentActivity info);
    /**
     * 新增子活动数据 少量数据
     * @param item
     * @return
     */
    @Insert(
            "INSERT INTO aat_agent_activity_detail " +
                    " (act_det_code,act_code,act_type,create_time,operator,status) " +
                    " VALUES " +
                    " (#{info.actDetCode},#{info.actCode},#{info.actType},now(),#{info.operator},#{info.status})"
    )
    int insertDetailLittle(@Param("info")AgentActivityDetail item);

    /**
     * 修改保存活动数据
     * @param info
     * @return
     */
    @Update(
            "update aat_agent_activity_detail set" +
                    " rank_desc=#{info.rankDesc},data_desc=#{info.dataDesc},active_ext=#{info.activeExt}" +
                    " where act_det_code=#{info.actDetCode}"
    )
    int editAgentActivityDetail(@Param("info")AgentActivityDetail info);

    /**
     * 更新子活动状态
     * @param item
     * @return
     */
    @Update(
            "update aat_agent_activity_detail set status=#{info.status} where act_det_code=#{info.actDetCode}"
    )
    int updateDetailStatus(@Param("info")AgentActivityDetail item);
    /**
     * 获取活动信息
     * @param actCode
     * @return 只有1条
     */
    @Select(
            " select * from aat_agent_activity where act_code=#{actCode}"
    )
    AgentActivity getAgentActivity(@Param("actCode")String actCode);

    /**
     * 获取子活动列表信息
     * @param actCode
     * @return 多条
     */
    @Select(
            " select * from aat_agent_activity_detail where act_code=#{actCode}"
    )
    List<AgentActivityDetail> getDetailList(@Param("actCode")String actCode);

    /**
     * 获取子活动数据
     * @param actDetCode
     * @return 单条数据
     */
    @Select(
            " select * from aat_agent_activity_detail where act_det_code=#{actDetCode} "
    )
    AgentActivityDetail getAgentActivityDetail(@Param("actDetCode")String actDetCode);

    /**
     * 查询奖品列表
     * @param actDetCode
     * @return 多条
     */
    @Select(
            " select * from aat_agent_act_reward_config where act_det_code=#{actDetCode} "
    )
    List<AgentActRewardConfig> getAgentActRewardConfigList(@Param("actDetCode")String actDetCode);

    /**
     * 新增奖品
     * @param item
     * @return
     */
    @Insert(
            "INSERT INTO aat_agent_act_reward_config " +
                    " (reward_code,act_code,act_det_code,reward_type,calc_type,reward_name,reward_num,rank_seq,create_time) " +
                    " VALUES " +
                    " (#{info.rewardCode},#{info.actCode},#{info.actDetCode},#{info.rewardType},#{info.calcType},#{info.rewardName},#{info.rewardNum},#{info.rankSeq},now()) "
    )
    int addRewardConfig(@Param("info")AgentActRewardConfig item);

    /**
     * 修改奖品
     * @param item
     * @return
     */
    @Update(
            "update aat_agent_act_reward_config set " +
                    " reward_type=#{info.rewardType},calc_type=#{info.calcType},reward_name=#{info.rewardName},reward_num=#{info.rewardNum},rank_seq=#{info.rankSeq} " +
                    " where id=#{info.id}"
    )
    int updateRewardConfig(@Param("info")AgentActRewardConfig item);

    /**
     * 删除多条数据
     * @param strs
     * @return
     */
    @Delete(
            "delete from aat_agent_act_reward_config where id in (${strs}) "
    )
    int deleteRewardConfig(@Param("strs")String strs);


    /**
     * 查询活动数据列表
     * @param info
     * @param page
     * @return
     */
    @SelectProvider(type= AgentActivityDao.SqlProvider.class,method="selectAgentActivityData")
    @ResultType(AgentActivityData.class)
    List<AgentActivityData> selectAgentActivityData(@Param("info")AgentActivityData info, @Param("page") Page<AgentActivityData> page,@Param("config")AgentActivityDetail config);

    /**
     * 查询活动数据导出列表
     * @param info
     * @return
     */
    @SelectProvider(type= AgentActivityDao.SqlProvider.class,method="selectAgentActivityData")
    @ResultType(AgentActivityData.class)
    List<AgentActivityData> exportActivityDataSelect(@Param("info")AgentActivityData info,@Param("config")AgentActivityDetail config);

    /**
     * 查询排行版实时
     * @param info
     * @param page
     * @param config
     * @return
     */
    @SelectProvider(type= AgentActivityDao.SqlProvider.class,method="selectAgentRankingList")
    @ResultType(AgentActivityData.class)
    List<AgentActivityData> selectAgentRankingList(@Param("info")AgentActivityData info, @Param("page")Page<AgentActivityData> page, @Param("config")AgentActivityDetail config);

    /**
     * 导出排行版
     * @param info
     * @param config
     * @return
     */
    @SelectProvider(type= AgentActivityDao.SqlProvider.class,method="selectAgentRankingList")
    @ResultType(AgentActivityData.class)
    List<AgentActivityData> exportAgentRankingListSelect(@Param("info")AgentActivityData info, @Param("config")AgentActivityDetail config);

    /**
     * 判断是否已定时生产排行数据
     */
    @Select(
            "select arl.id " +
                    "  from aat_agent_ranking_list arl " +
                    "    INNER JOIN aat_agent_activity_detail aad ON (aad.act_code=arl.act_code and aad.act_det_code=arl.act_det_code)  " +
                    "  where arl.act_code=#{info.actCode} and aad.act_type=#{info.actType} " +
                    " LIMIT 1 "
    )
    Long checkRankingList(@Param("info")AgentActivityData info);

    /**
     * 查询定时生成的排行版数据
     * 配置值都取实时配置得
     * @param info
     * @param page
     * @return
     */
    @SelectProvider(type= AgentActivityDao.SqlProvider.class,method="selectRankingListOld")
    @ResultType(AgentActivityData.class)
    List<AgentActivityData> selectRankingListOld(@Param("info")AgentActivityData info, @Param("info")Page<AgentActivityData> page);

    /**
     * 导出定时生成的排行版数据
     * 配置值都取实时配置得
     * @param info
     * @return
     */
    @SelectProvider(type= AgentActivityDao.SqlProvider.class,method="selectRankingListOld")
    @ResultType(AgentActivityData.class)
    List<AgentActivityData> exportRankingListOld(@Param("info")AgentActivityData info);


    /**
     * 删除活动数据
     * @param actCode
     * @return
     */
    @Delete(
            " delete from aat_agent_activity where act_code=#{actCode}"
    )
    int deleteAgentActivityByCode(@Param("actCode")String actCode);

    /**
     * 删除子活动数据
     * @param actCode
     * @return
     */
    @Delete(
            " delete from aat_agent_activity_detail where act_code=#{actCode}"
    )
    int deleteAgentActivityDetailByCode(@Param("actCode")String actCode);


    /**
     * 删除奖品数据
     * @param actCode
     * @return
     */
    @Delete(
            " delete from aat_agent_act_reward_config where act_code=#{actCode}"
    )
    int deleteRewardConfigByCode(@Param("actCode")String actCode);

    class SqlProvider{


        /**
         * 排行版已生成
         * @param param
         * @return
         */
        public String selectRankingListOld(final Map<String, Object> param) {
            AgentActivityData info = (AgentActivityData) param.get("info");
            StringBuffer sb=new StringBuffer();
            sb.append("select ac.*,  ");
            sb.append("       arl.rank_seq,arl.reward_code,arl.reward_name AS rewardStr, ");
            //直接回显
            if("1".equals(info.getActType())){
                sb.append(" IFNULL(round(arl.bullish_value,0),0) AS score,");
            }else{
                sb.append(" IFNULL(round(arl.heroism_value,0),0) AS score,");
                sb.append(" IF(arl.offer_reward_value is not null,arl.offer_reward_value,'-') AS rewardConfig, ");
                sb.append(" CONCAT(truncate(IFNULL(arl.join_ratio_value,0)*100,2),'%') AS joinRatio,");//显示值 比例截取
            }
            //商户数
            if("1".equals(info.getActType())){
                sb.append("  ac.total_under_mers AS totalMersStr, ");
                sb.append("  ac.total_under_trans AS totalTransStr, ");
                sb.append("  ac.total_under_act_mers AS totalActMersStr, ");
                sb.append("  IFNULL(round((ac.total_under_act_trans/ac.total_under_act_mers),2),0) AS totalAverageTrans, ");
                sb.append("  ac.total_under_act_trans AS totalActTransStr, ");
                sb.append("  (ac.total_under_mers - ac.total_under_act_mers) AS totalNoActMers, ");
                sb.append("  IFNULL(round(((ac.total_under_trans - ac.total_under_act_trans)/(ac.total_under_mers-ac.total_under_act_mers)),2),0) AS totalAverageNoActTrans, ");
            }else{
                sb.append("  ac.total_mers AS totalMersStr, ");
                sb.append("  ac.total_trans AS totalTransStr, ");
                sb.append("  ac.total_act_mers AS totalActMersStr, ");
                sb.append("  IFNULL(round((ac.total_act_trans/ac.total_act_mers),2),0) AS totalAverageTrans, ");
                sb.append("  ac.total_act_trans AS totalActTransStr, ");
                sb.append("  (ac.total_mers - ac.total_act_mers) AS totalNoActMers, ");
                sb.append("  IFNULL(round(((ac.total_trans - ac.total_act_trans)/(ac.total_mers-ac.total_act_mers)),2),0) AS totalAverageNoActTrans, ");
            }
            sb.append("     ai.agent_name,ai.mobilephone,ai.agent_level,ai2.agent_no AS oneAgentNo,ai2.agent_name AS oneAgentName,ai2.sale_name AS oneSaleName");
            sb.append(" from aat_agent_ranking_list arl ");
            sb.append("    LEFT JOIN aat_agent_act_census ac ON (ac.act_code=arl.act_code and ac.agent_no=arl.agent_no)");
            sb.append("    LEFT JOIN aat_agent_activity_detail aad ON aad.act_det_code=arl.act_det_code ");
            sb.append("    LEFT JOIN agent_info ai ON ai.agent_no=arl.agent_no ");
            sb.append("    LEFT JOIN agent_info ai2 ON ai2.agent_no=ai.one_level_id ");
            sb.append(" where arl.act_code=#{info.actCode} ");//数据必须有某个活动
            if(StringUtils.isNotBlank(info.getAgentNo())){
                sb.append("   and  arl.agent_no=#{info.agentNo}");
            }
            sb.append("       and  aad.act_type=#{info.actType}");
            sb.append(" order BY arl.rank_seq ");
            return sb.toString();
        }

        /**
         * 查询排行版实时
         * @param param
         * @return
         */
        public String selectAgentRankingList(final Map<String, Object> param) {
            AgentActivityData info = (AgentActivityData) param.get("info");
            AgentActivityDetail config = (AgentActivityDetail) param.get("config");
            StringBuffer sb=new StringBuffer();
            //适应统计框架
            sb.append("select t3.* from ( ");
            sb.append("    select t1.*,  ");
            if("1".equals(info.getActType())){
                sb.append("        IF(t2.reward_type='1',CONCAT(t2.reward_num,'元'),t2.reward_name) AS rewardStr, ");
            }else{
                sb.append("        IF(t2.calc_type='2',CONCAT(t2.reward_num,'元'),'-') AS rewardConfig, ");
                sb.append("        IF(t2.reward_type='1',IF(t2.calc_type='2',CONCAT(round(t2.reward_num*t1.joinRatio1,2),'元'),CONCAT(t2.reward_num,'元')),t2.reward_name) AS rewardStr, ");
            }
            sb.append("        t2.reward_code,t2.reward_type,t2.calc_type,t2.reward_name,t2.reward_num ");
            sb.append("     FROM(  ");
            sb.append("          select t.*,(@rowNum:=@rowNum+1) AS rankSeq  ");
            sb.append("           FROM (  ");
            sb.append("                select ac.*,  ");
            if("1".equals(info.getActType())){
                sb.append("              IFNULL(round((ac.total_under_act_mers / ac.total_under_mers)*"+config.getActiveExtMap().get("coefficient1")+"*"+config.getActiveExtMap().get("coefficient2")+",0),0) AS score,");
                sb.append("              IFNULL((ac.total_under_act_mers / ac.total_under_mers)*"+config.getActiveExtMap().get("coefficient1")+"*"+config.getActiveExtMap().get("coefficient2")+",0) AS score1,");
            }else{
                sb.append("              round((ac.total_act_trans / 10000000)*"+config.getActiveExtMap().get("coefficient")+",0) AS score,");
                sb.append("              (ac.total_act_trans / 10000000)*"+config.getActiveExtMap().get("coefficient")+" AS score1,");
                sb.append("              CONCAT(truncate((ac.total_act_mers / ac.total_mers)*100,2),'%') AS joinRatio,");//显示值 比例截取
                sb.append("              (ac.total_act_mers / ac.total_mers) AS joinRatio1,");//方便计算
                sb.append("              truncate((ac.total_act_mers / ac.total_mers)*100,2) AS joinRatio2,");//排序 比例截取
            }
            //商户数
            if("1".equals(info.getActType())){
                sb.append("             ac.total_under_mers AS totalMersStr, ");
                sb.append("             ac.total_under_trans AS totalTransStr, ");
                sb.append("             ac.total_under_act_mers AS totalActMersStr, ");
                sb.append("             IFNULL(round((ac.total_under_act_trans/ac.total_under_act_mers),2),0) AS totalAverageTrans, ");
                sb.append("             ac.total_under_act_trans AS totalActTransStr, ");
                sb.append("             (ac.total_under_mers - ac.total_under_act_mers) AS totalNoActMers, ");
                sb.append("             IFNULL(round(((ac.total_under_trans - ac.total_under_act_trans)/(ac.total_under_mers-ac.total_under_act_mers)),2),0) AS totalAverageNoActTrans, ");
            }else{
                sb.append("             ac.total_mers AS totalMersStr, ");
                sb.append("             ac.total_trans AS totalTransStr, ");
                sb.append("             ac.total_act_mers AS totalActMersStr, ");
                sb.append("             IFNULL(round((ac.total_act_trans/ac.total_act_mers),2),0) AS totalAverageTrans, ");
                sb.append("             ac.total_act_trans AS totalActTransStr, ");
                sb.append("             (ac.total_mers - ac.total_act_mers) AS totalNoActMers, ");
                sb.append("             IFNULL(round(((ac.total_trans - ac.total_act_trans)/(ac.total_mers-ac.total_act_mers)),2),0) AS totalAverageNoActTrans, ");
            }
            sb.append("                 ai.agent_name,ai.mobilephone,ai.agent_level,ai.create_date,ai2.agent_no AS oneAgentNo,ai2.agent_name AS oneAgentName,ai2.sale_name AS oneSaleName ");
            sb.append("               FROM aat_agent_act_census ac  ");
            sb.append("                 LEFT JOIN agent_info ai ON ai.agent_no=ac.agent_no ");
            sb.append("                 LEFT JOIN agent_info ai2 ON ai2.agent_no=ai.one_level_id ");
            sb.append("              ");
            sb.append("               where ac.act_code = #{info.actCode}  ");
            if("1".equals(info.getActType())){
                sb.append("                 and ac.total_under_act_mers>="+config.getActiveExtMap().get("merchantNum"));
            }else{
                sb.append("                 and ac.total_act_trans>0 ");
            }
            sb.append("               ) t,(SELECT(@rowNum := 0)) b ");
            if("1".equals(info.getActType())){
                sb.append("         ORDER BY t.score1 DESC,t.total_under_act_mers DESC,t.total_under_act_trans DESC,t.create_date asc  ");
            }else{
                sb.append("         ORDER BY t.score1 DESC,t.joinRatio1 DESC,t.total_act_mers DESC,t.create_date asc  ");
            }
            sb.append("      ) t1 ");
            sb.append("     LEFT JOIN (select arc.reward_code,arc.act_code,arc.act_det_code,arc.reward_type,arc.calc_type,arc.reward_name,arc.reward_num,arc.rank_seq  ");
            sb.append("                      from aat_agent_act_reward_config arc   ");
            sb.append("                         INNER JOIN aat_agent_activity_detail aad ON aad.act_det_code = arc.act_det_code  ");
            if("1".equals(info.getActType())){
                sb.append("                  where  arc.act_code = #{info.actCode} and aad.act_type='1' ");
            }else{
                sb.append("                  where  arc.act_code = #{info.actCode} and aad.act_type='2' ");
            }
            sb.append("     ) t2 ON (t2.act_code = t1.act_code AND t2.rank_seq = t1.rankSeq ) ");

            if(StringUtils.isNotBlank(info.getAgentNo())){
                sb.append(" where  t1.agent_no=#{info.agentNo}");
            }
            sb.append("  ) t3 ORDER BY t3.rankSeq ");
            return sb.toString();
        }
        /**
         * 查询活动数据
         * @param param
         * @return
         */
        public String selectAgentActivityData(final Map<String, Object> param) {
            AgentActivityData info = (AgentActivityData) param.get("info");
            AgentActivityDetail config = (AgentActivityDetail) param.get("config");
            StringBuffer sb=new StringBuffer();
            sb.append("select ac.*,  ");
            if(config.getActiveExtMap()!=null){
                if("1".equals(info.getActType())){
                    sb.append(" IFNULL(round((ac.total_under_act_mers / ac.total_under_mers)*"+config.getActiveExtMap().get("coefficient1")+"*"+config.getActiveExtMap().get("coefficient2")+",0),0) AS score,");
                }else{
                    sb.append(" round((ac.total_act_trans / 10000000)*"+config.getActiveExtMap().get("coefficient")+",0) AS score,");
                    sb.append(" CONCAT(truncate((ac.total_act_mers / ac.total_mers)*100,2),'%') AS joinRatio,");//显示值
                }
            }
            //商户数
            if("1".equals(info.getActType())){
                sb.append("     ac.total_under_mers AS totalMersStr, ");
                sb.append("     ac.total_under_trans AS totalTransStr, ");
                sb.append("     ac.total_under_act_mers AS totalActMersStr, ");
                sb.append("     IFNULL(round((ac.total_under_act_trans/ac.total_under_act_mers),2),0) AS totalAverageTrans, ");
                sb.append("     ac.total_under_act_trans AS totalActTransStr, ");
                sb.append("     (ac.total_under_mers - ac.total_under_act_mers) AS totalNoActMers, ");
                sb.append("     IFNULL(round(((ac.total_under_trans - ac.total_under_act_trans)/(ac.total_under_mers-ac.total_under_act_mers)),2),0) AS totalAverageNoActTrans, ");
            }else{
                sb.append("     ac.total_mers AS totalMersStr, ");
                sb.append("     ac.total_trans AS totalTransStr, ");
                sb.append("     ac.total_act_mers AS totalActMersStr, ");
                sb.append("     IFNULL(round((ac.total_act_trans/ac.total_act_mers),2),0) AS totalAverageTrans, ");
                sb.append("     ac.total_act_trans AS totalActTransStr, ");
                sb.append("     (ac.total_mers - ac.total_act_mers) AS totalNoActMers, ");
                sb.append("     IFNULL(round(((ac.total_trans - ac.total_act_trans)/(ac.total_mers-ac.total_act_mers)),2),0) AS totalAverageNoActTrans, ");
            }
            sb.append("      ai.agent_name,ai.mobilephone,ai.agent_level,ai2.agent_no AS oneAgentNo,ai2.agent_name AS oneAgentName,ai2.sale_name AS oneSaleName");
            sb.append(" from aat_agent_act_census ac ");
            sb.append("    LEFT JOIN agent_info ai ON ai.agent_no=ac.agent_no ");
            sb.append("    LEFT JOIN agent_info ai2 ON ai2.agent_no=ai.one_level_id ");
            sb.append(" where ac.act_code=#{info.actCode} ");//数据必须有某个活动
            if(StringUtils.isNotBlank(info.getAgentNo())){
                sb.append("   and  ac.agent_no=#{info.agentNo}");
            }
            sb.append(" order BY ac.agent_no ");
            return sb.toString();
        }

        /**
         * 查询活动列表SQL
         * @param param
         * @return
         */
        public String selectAllList(final Map<String, Object> param) {
            final AgentActivity info = (AgentActivity) param.get("info");
            return new SQL(){{
                SELECT("*");
                FROM("aat_agent_activity");
                if(StringUtils.isNotBlank(info.getActName())){
                    WHERE("act_name like concat('%',#{info.actName},'%') ");
                }
                if (info.getCreateTimeBegin() != null) {
                    WHERE("create_time >=#{info.createTimeBegin}");
                }
                if (info.getCreateTimeEnd() != null) {
                    WHERE("create_time <=#{info.createTimeEnd}");
                }
                if(info.getOpenStatus()!=null){
                    WHERE("open_status=#{info.openStatus}");
                }
                ORDER_BY("create_time desc");
            }}.toString();
        }
    }
}
